﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Web.Configuration;
using Recon.Portal.DataObjects;
namespace Recon.Portal.Data
{
    public class RemitAccountDAOImpl:RemitAccountDAO
    {
         private SqlConnection conn = null;

         public RemitAccountDAOImpl()
        {
            object connString = WebConfigurationManager.ConnectionStrings["SqlConnection"];
            if (connString != null)
            {
                conn = new SqlConnection(connString.ToString());
            }
        }
         public List<RemitAccount> GetAllRemitterAccountsForCustomer(String CustomerID)
        {
            List<RemitAccount> accountLst = null;
            try
            {
                String sqlQuery = "SELECT remitter_id,Account_number,Account_name,bank_id,customer_id from mast_remitter_account_maint where customer_id = '" + CustomerID + "'";
                conn.Open();
                SqlCommand cmd;
                cmd = new SqlCommand(sqlQuery, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    int columnIndx = 0;
                    accountLst = new List<RemitAccount>();

                    while (reader.Read())
                    {
                        columnIndx = 0;
                        RemitAccount account = new RemitAccount();
                        account.RemitID = reader.GetString(columnIndx++);
                        account.AccountNumber = reader.GetString(columnIndx++);
                        account.AccountName = reader.GetString(columnIndx++);
                        account.BankID = reader.GetString(columnIndx++);
                        account.CustID = reader.GetString(columnIndx++);
                        // others need to be populated
                        accountLst.Add(account);
                    }
                }

            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return accountLst;
        }
         public List<RemitAccount> GetAllRemitAccounts(String RemitID, String AccountNo, String CustId)
        {
            List<RemitAccount> accountLst = null;
            //customer_id colun will store remitter id also but type would be c= customer & R = remitter
            try
            {
                String sqlQuery = "SELECT remitter_id,Account_number,Account_name,bank_id,customer_id from mast_remitter_account_maint where remitter_id = '" + RemitID + "' and account_number = '" + AccountNo + "'" +
                    " and customer_id = '" + CustId  + "'";
                conn.Open();
                SqlCommand cmd;
                cmd = new SqlCommand(sqlQuery, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    int columnIndx = 0;
                    accountLst = new List<RemitAccount>();

                    while (reader.Read())
                    {
                        columnIndx = 0;
                        RemitAccount account = new RemitAccount();
                        account.RemitID = reader.GetString(columnIndx++);
                        account.AccountNumber = reader.GetString(columnIndx++);
                        account.AccountName = reader.GetString(columnIndx++);
                        account.BankID = reader.GetString(columnIndx++);
                        account.CustID = reader.GetString(columnIndx++);
                        // others need to be populated
                        accountLst.Add(account);
                    }
                }

            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return accountLst;
            
        }
        
      
         public List<RemitAccount> GetAllAccountsForBank(String BankID)
        {
            return null;
        }
         public int Insert(RemitAccount account)
        {
            int retval = 0;
            string strQuery = "";

            strQuery = "insert into mast_remitter_account_maint(remitter_id,account_number,account_name,bank_id,customer_id)" +
                   " values('" + account.RemitID + "','" + account.AccountNumber + "','" + account.AccountName + "','" + account.BankID + "','" + account.CustID + "')";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
            }

            return retval;
           
        }

         public int Update(RemitAccount account)
        {
            int retval = 0;
            string strQuery = "";

            strQuery = "Update mast_remitter_account_maint set account_name = '" + account.AccountName + "'" +
                   " Where customer_id = '" + account.CustID + "' and account_number = '" + account.AccountNumber + "' and remitter_id = '" + account.RemitID  + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

         public int Delete(RemitAccount account)
        {
            int retval = 0;
            string strQuery = "";
            //customer_id,account_number,
            strQuery = "Delete From mast_remitter_account_maint Where customer_id = '" + account.CustID + "' and account_number = '" + account.AccountNumber + "' and remitter_id = '" + account.RemitID + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }
    }
}
